package OnlineExam.setup;

import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.struts.action.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.io.*;
import OnlineExam.configuration.*;
import static OnlineExam.configuration.Configure.*;


/**
 *  Sets up the tables needed for the application along with the administrator attribute initialization.
 */


public final class SetupAction extends Action 
{
	public static final String dbName = "OnlineExam";
	/**
	 * sets up the required tables.
	 */	
	
	private void setupTables(Connection connection, String admin, String adminPassword) throws SQLException
	{
    	Statement statement = connection.createStatement ();
		
		String adminTable = "create table users (Username varchar(30) not null, Password varchar(20) not null)";
	    statement.execute(adminTable);
			
		String adminMapRole= "create table user_roles (Username varchar(30) not null, role_name varchar(20) not null)";
		statement.execute(adminMapRole);
		
		String queryToAddAdmin = "insert into users values ('" + admin + "','" + adminPassword + "')";
		statement.execute(queryToAddAdmin);
		
		String queryToAssignRole = "insert into user_roles values ('" + admin + "','Admin')";
		statement.execute(queryToAssignRole);
		
		String createCategoryTable = "create table Category (CategoryId varchar(20) not null primary key, CategoryName varchar(30) not null, CategoryAdmin varchar(30) not null, TestLength int not null, TimeLimit int not null)";
		statement.execute(createCategoryTable);
		String createQuestionsTable = "create table Questions (Number int not null primary key auto_increment, CategoryId varchar(20) not null, Question text not null, Options text not null, CorrectAnswer char(4) not null, Explanation text, CorrectMarks int not null, WrongMarks int not null)";
		statement.execute(createQuestionsTable);
		
		String createTestsTable = "create table Tests (TestId varchar(30) not null primary key, CategoryId varchar(20) not null, Username varchar(30) not null, Date date not null, MarksScored int, MaximumMarks int, StartTime mediumtext not null, EndTime mediumtext not null)";
		statement.execute (createTestsTable);
			
		String createQuestionAnswerTable = "create table QuestionAnswer (TestId varchar(30) not null, QuestionNumber int not null, Answer char(4), Explanation text)";
		statement.execute(createQuestionAnswerTable);
	}
	
	/**
	 * create the database for setup assuming the database does not exist.
	 */ 	
	
	private void createDB () throws SQLException
	{
		Connection connection = null;
		try 
		{
			connection = DriverManager.getConnection (urlRoot + "/" + initialConnectDBName, username, password);
   			Statement statement = connection.createStatement ();

			String createDB = "create database " + dbName;
			statement.execute(createDB);	
		}
		finally 
		{
			try { if (connection != null) connection.close (); } 
			catch (SQLException e) { System.out.println ("SQLException "+e); }
		}
	}
	
	/**
	 *  gets the Admin username and password and sets up the required tables.
	 */
	
	private void setupDB (SetupForm f) throws SQLException
	{
        String admin = f.getUsername ();
		String adminPassword = f.getPassword ();
		Connection connection = null;

		try 
		{
			// now create a new connection to the newly created db
		 	connection = DriverManager.getConnection (urlRoot + "/" + dbName, username, password);
			setupTables (connection, admin, adminPassword);
			//return (mapping.findForward("success"));
		} 
		finally 
		{
			try { if (connection != null) connection.close (); } 
			catch (SQLException e) { System.out.println ("SQLException "+e); }
		}	
	}

	/**
	 * checks if the database is existing.
	 */

	private boolean dbExists()
	{

		Connection connection = null;
		try
		{
			connection = DriverManager.getConnection (urlRoot + "/" + dbName, username, password);
			return true;
		}
		catch (SQLException sqle)
		{
			System.out.println ("sqlexception : " + sqle);
			return false;
		}
		finally 
		{
			try { if (connection != null) connection.close (); } 
			catch (SQLException e) { System.out.println ("SQLException "+e); }
		}
	}
	
    /** 
	 *  provide the business logic here.
	 */

   	 public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, 
					 HttpServletResponse response) throws SQLException, IOException, ServletException, NullPointerException
	 {

		SetupForm f = (SetupForm) form;
		
	
		// this is to check if the database is existing i.e if the setup is already done.
		// 

		if (dbExists())
		{
			return (mapping.findForward("failure"));
		}
		else
		{
			try
			{
				createDB ();	
				setupDB (f);
				return (mapping.findForward("success"));
			}
			catch (SQLException sqle)
			{
				System.out.println ("SQLException : " + sqle);
				return (mapping.findForward("dbError"));
			}
		}
				
    }
}
